#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class FAQDAL : BaseDAL
	 {
         #region ObjIDataReader

         /// <summary>
         /// Returns a new Products instance filled with the DataReader's current record data 
         /// </summary>
         private FAQ GetFAQFromReader(IDataReader dr)
         {
             FAQ obj = new FAQ();
             obj.FAQID = (dr["FAQID"] is DBNull) ? Int32.MinValue : (Int32)dr["FAQID"];
             //obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["AddedDate"];
             obj.FullName = (dr["FullName"] is DBNull) ? String.Empty : (String)dr["FullName"];
             obj.Phone = (dr["Phone"] is DBNull) ? String.Empty : (String)dr["Phone"];
             obj.Email = (dr["Email"] is DBNull) ? String.Empty : (String)dr["Email"];
             obj.Questions = (dr["Questions"] is DBNull) ? String.Empty : (String)dr["Questions"];
             obj.QuestionsDate = (dr["QuestionsDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["QuestionsDate"];
             obj.Answer = (dr["Answer"] is DBNull) ? String.Empty : (String)dr["Answer"];
             obj.AnswerDate = (dr["AnswerDate"] is DBNull) ? DateTime.MinValue : (DateTime)dr["AnswerDate"];
             obj.AddedBy = (dr["AddedBy"] is DBNull) ? String.Empty : (String)dr["AddedBy"];
             obj.Answered = (dr["Answered"] is DBNull) ? false : (Boolean)dr["Answered"];
             obj.Display = (dr["Display"] is DBNull) ? false : (Boolean)dr["Display"];
             obj.IDCate = (dr["IDCate"] is DBNull) ? String.Empty : (String)dr["IDCate"];
             obj.LanguageCode = (dr["LanguageCode"] is DBNull) ? String.Empty : (String)dr["LanguageCode"];
             
             return obj;
         }
         #endregion


	     /// <summary>
	     /// Returns a collection of Products with the data read from the input DataReader
	     /// </summary>
	     private List<FAQ> GetFAQCollectionFromReader(IDataReader dr)
	     {
		     List<FAQ> obj = new List<FAQ>();
		     while(dr.Read())
			     obj.Add(GetFAQFromReader(dr));
		     return obj;
	     }


        
         public List<FAQ> Search(string Keyword)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 List<FAQ> list = new List<FAQ>();
                 string sql = "Select * from w4u_FrequentlyAskedQuestions where FAQID > 0 ";
                 if (Keyword.Trim().Length > 0)
                     sql = sql + " And( (Questions Like N'%" + Keyword + "%') Or  (Answer Like N'%" + Keyword + "%')) ";

                 sql = sql + " Order by FAQID Desc";

                 SqlCommand myCommand = new SqlCommand(sql, myConn);
                 myCommand.CommandType = CommandType.Text;
                 SqlDataReader dr = myCommand.ExecuteReader();
                 return GetFAQCollectionFromReader(dr);
             }
         }

         public int Insert(FAQ obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_FrequentlyAskedQuestions_INSERT", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@FAQID", SqlDbType.Int).Direction = ParameterDirection.Output;
                 myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
                 myCommand.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = obj.Phone;
                 myCommand.Parameters.Add("@Email", SqlDbType.NVarChar).Value = obj.Email;
                 myCommand.Parameters.Add("@Questions", SqlDbType.NVarChar).Value = obj.Questions;
                 myCommand.Parameters.Add("@QuestionsDate", SqlDbType.DateTime).Value = obj.QuestionsDate;
                 myCommand.Parameters.Add("@Answer", SqlDbType.NText).Value = obj.Answer;
                 myCommand.Parameters.Add("@AnswerDate", SqlDbType.DateTime).Value = obj.AnswerDate;
                 myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                 myCommand.Parameters.Add("@Answered", SqlDbType.Bit).Value = obj.Answered;
                 myCommand.Parameters.Add("@Display", SqlDbType.Bit).Value = obj.Display;
                 myCommand.Parameters.Add("IDCate", SqlDbType.NVarChar).Value = obj.IDCate;
                 myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = obj.LanguageCode;
                 
                 int ret = myCommand.ExecuteNonQuery();
                 return (int)myCommand.Parameters["@FAQID"].Value;
             }
         }

         
         public bool Update(FAQ obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_FrequentlyAskedQuestions_UPDATE", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@FAQID", SqlDbType.Int).Value = obj.FAQID;
                 myCommand.Parameters.Add("@FullName", SqlDbType.NVarChar).Value = obj.FullName;
                 myCommand.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = obj.Phone;
                 myCommand.Parameters.Add("@Email", SqlDbType.NVarChar).Value = obj.Email;
                 myCommand.Parameters.Add("@Questions", SqlDbType.NVarChar).Value = obj.Questions;
                 myCommand.Parameters.Add("@QuestionsDate", SqlDbType.DateTime).Value = obj.QuestionsDate;
                 myCommand.Parameters.Add("@Answer", SqlDbType.NText).Value = obj.Answer;
                 myCommand.Parameters.Add("@AnswerDate", SqlDbType.DateTime).Value = obj.AnswerDate;
                 myCommand.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = obj.AddedBy;
                 myCommand.Parameters.Add("@Answered", SqlDbType.Bit).Value = obj.Answered;
                 myCommand.Parameters.Add("@Display", SqlDbType.Bit).Value = obj.Display;
                 myCommand.Parameters.Add("IDCate", SqlDbType.NVarChar).Value = obj.IDCate;
                 myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = obj.LanguageCode;
                 
                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


	     public bool Delete(Int32 FAQID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
                 SqlCommand myCommand = new SqlCommand("w4u_FrequentlyAskedQuestions_DELETE", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@FAQID", SqlDbType.Int).Value = FAQID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
         /// Retrieves the w4u_FrequentlyAskedQuestions with the specified ID
	     /// </summary>
	     public FAQ GetFAQByID(Int32 FAQID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
                 SqlCommand myCommand = new SqlCommand("w4u_FrequentlyAskedQuestions_getFAQByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@FAQID", SqlDbType.Int).Value = FAQID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetFAQFromReader(dr) : null);
		     }
	     }

	     //Get All FAQ
	     public List<FAQ> GetAllFAQ(int PageIndex, int PageSize, string LanguageCode)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
                 SqlCommand myCommand = new SqlCommand("w4u_FrequentlyAskedQuestions_GetAllFAQ", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
             myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = LanguageCode;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetFAQCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  FAQ
	     public int GetCountFAQ(string LanguageCode)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
                 SqlCommand myCommand = new SqlCommand("w4u_FrequentlyAskedQuestions_GetCountFAQ", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure;
             myCommand.Parameters.Add("@LanguageCode", SqlDbType.NVarChar).Value = LanguageCode;
		     return (int)myCommand.ExecuteScalar();
		     }
	     }
         

	 }
}
